package com.tl.sboot.utils;

import java.io.File;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Scanner;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

public class StkSqlUtil {
	public static final String DESC = "desc";
	public static final String ASC = "asc";
	private String sort = DESC;
	private Integer Top;//取多少条
	private Integer beginNumber;//从多少条开始
	private String minAndMax;
	static Map<String, String> fieldNames;
	/**
	 * 
	 * @param operation  运算
	 * @param sort	排序
	 * @param Top	取前多少条
	 * @param mixAndMax 最大值和最小值(中间以=隔开)
	 */
	public StkSqlUtil(String sort,Integer beginNumber,Integer Top,String minAndMax){
		this.sort = sort;
		this.Top = Top;
		this.beginNumber = beginNumber;
		this.minAndMax = minAndMax;
	}
    /**
     * 读取json文件
     * @return
     */
    public static String readJson(){
    	String fullFileName = "src/main/resources/result.json";
        File file = new File(fullFileName);
        Scanner scanner = null;
        StringBuilder buffer = new StringBuilder();
        try {
            scanner = new Scanner(file, "utf-8");
            while (scanner.hasNextLine()) {
                buffer.append(scanner.nextLine());
            }
        } catch (Exception e) {
        	e.printStackTrace();
        } finally {
            if (scanner != null) {
                scanner.close();
            }
        }
        return buffer.toString();
    }
    
    /**
     * 通过字段中文名字得到数据库表中的字段名
     * @param name
     * @return
     */
    public String getFieldName(String name){
    	if (fieldNames==null) {
    		fieldNames = getFildNames();
		}
    	return fieldNames.get(name);
    }
    
    /**
     * 得到所有的INT或者 NUMERIC的字段键值对
     * @return
     */
    public Map<String, String> getFildNames(){
    	Map<String, String> myMap = new HashMap<String, String>();
		String readJson = readJson();
		JSONArray jsonObject = JSONArray.fromObject(readJson);
		for (Object object : jsonObject) {
			JSONObject tableObj = (JSONObject) object;
			String tableName = tableObj.getString("tablename");
			JSONArray filedsObj = tableObj.getJSONArray("fields");
			for (Object subFields : filedsObj) {
				JSONObject sub = (JSONObject) subFields;
				String type = sub.getString("type");
				if (type.equals("INT") || type.equals("NUMERIC")) {
					myMap.put(sub.getString("name"), tableName+"."+sub.getString("fields"));
				}
			}
		} 
		return myMap;
    }
    
    /**
     * 获取动态sql
     * @return
     */
    public String getSql(String operationFields){
    	String key = MD5Tools.MD5(operationFields);
    	key = "operationResult";
    	String joinSql = getTableJoinSql(operationFields);
    	String sql = "select distinct STK_CODE.STOCKSNAME,STK_CODE.STOCKCODE,STK_CODE.TRADE_MKT,STK_CODE.COMCODE,"+operationFields+" as "+ key+" from stk_code "+joinSql+" and STATUS_TYPE_REF=1 "+getTableWhere(operationFields, minAndMax)+" order by "+ key +" "+ sort;
    	if (Top!=0) {
			sql += " limit "+beginNumber+","+Top;
		}
    	System.out.println(sql);
    	return sql;
    }
    
    /**
     * 获取表连接sql
     * @return
     */
    public String getTableJoinSql(String operation){
    	Set<String> tableNames = new HashSet<String>();
    	String[] split = operation.split("[- + * / ( )]");
    	for (String string : split) {
    		if (!string.equals("")) {
    			String tableName = string.substring(0,string.indexOf("."));
    			tableNames.add(tableName);
			}
		}
    	//便利表连接
    	StringBuffer buffer = new StringBuffer();
    	StringBuffer buffer2 = new StringBuffer();
    	for (String tableName : tableNames) {
    		buffer.append("inner join "+ tableName +" on "+tableName+".COMCODE = STK_CODE.COMCODE ");
    		buffer2.append("and "+tableName+".RPT_SRC = '年报' and "+tableName+".RPT_DATE =(SELECT MAX(RPT_DATE) FROM "+tableName+" a where a.RPT_SRC='年报' and a.COMCODE="+tableName+".COMCODE)"
    				+ " and "+tableName+".ENDDATE =(SELECT MAX(RPT_DATE) FROM "+tableName+" a where a.RPT_SRC='年报' and a.COMCODE="+tableName+".COMCODE) and "+tableName+".RPT_TYPE='合并' ");
		}
    	String sqlJoin = buffer.toString()+buffer2.toString();
    	return sqlJoin;
    }
    /**
     * 获取运算条件
     * @return
     */
    public String getTableWhere(String operationFields,String minAndMax){
    	if (minAndMax.equals("")) {
			return "";
		}
    	int to = minAndMax.indexOf("=");
		String min = minAndMax.substring(0,to);
		String max = minAndMax.substring(to+1); 
    	return "where "+operationFields+" between "+min+" and "+max;
    }
    /**
     * 通过name获取fields
     * @param param
     * @return
     */
    public String getOperationFields(String param){
    	String regEx="[- | + | * | / | ( | )]"; 
    	Pattern p =Pattern.compile(regEx);
    	Matcher m = p.matcher(param); 
    	String[] names = p.split(param);
    	for (int i = 0; i < names.length; i++) {
    		names[i] = getFieldName(names[i]);
    		if(m.find()){  
	        	names[i] +=m.group();  
	        }  
		}
    	StringBuffer operation = new StringBuffer();
    	for (String string : names) {
			operation.append(string);
		}
    	String strOperation = operation.toString().replaceAll("null", "");
    	return strOperation;
    }
	

    public static void main(String[] args) {
    	/*StkSqlUtil sqlUtil = new StkSqlUtil(StkSqlUtil.DESC, 10);
    	String param = "(营业总收入-(营业收入-折扣与折让)*折扣与折让)";
    	String operationFields = sqlUtil.getOperationFields(param);
    	String sql = sqlUtil.getSql(operationFields);*/
    	
    	
	}
}
